/**
 * 用户端数据库初始化脚本
 * 创建用户端专用的数据库表结构
 */

const { query } = require('../config/database');

async function initializeUserDatabase() {
    try {
        console.log('🚀 开始初始化用户端数据库...');

        // 创建地址表（用户端专用）
        const createAddressTableQuery = `
            CREATE TABLE IF NOT EXISTS addresses (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                recipient_name VARCHAR(100) NOT NULL,
                phone_number VARCHAR(20) NOT NULL,
                country VARCHAR(50) NOT NULL DEFAULT '中国',
                province VARCHAR(50) NOT NULL,
                city VARCHAR(50) NOT NULL,
                district VARCHAR(50),
                postal_code VARCHAR(10) NOT NULL,
                detail_address TEXT NOT NULL,
                address_label VARCHAR(20) DEFAULT 'home',
                is_default BOOLEAN DEFAULT FALSE,
                is_active BOOLEAN DEFAULT TRUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
        `;

        await query(createAddressTableQuery);
        console.log('✅ 地址表创建完成');

        // 创建订单商品表（用户端专用）
        const createOrderItemsTableQuery = `
            CREATE TABLE IF NOT EXISTS order_items (
                id SERIAL PRIMARY KEY,
                order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
                product_id INTEGER NOT NULL REFERENCES products(id),
                quantity INTEGER NOT NULL CHECK (quantity > 0),
                price DECIMAL(10,2) NOT NULL,
                subtotal DECIMAL(10,2) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
        `;

        await query(createOrderItemsTableQuery);
        console.log('✅ 订单商品表创建完成');

        // 创建索引
        const createIndexesQuery = `
            -- 地址表索引
            CREATE INDEX IF NOT EXISTS idx_addresses_user_id ON addresses(user_id);
            CREATE INDEX IF NOT EXISTS idx_addresses_is_default ON addresses(is_default);
            CREATE INDEX IF NOT EXISTS idx_addresses_is_active ON addresses(is_active);
            
            -- 订单商品表索引
            CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items(order_id);
            CREATE INDEX IF NOT EXISTS idx_order_items_product_id ON order_items(product_id);
        `;

        await query(createIndexesQuery);
        console.log('✅ 数据库索引创建完成');

        console.log('🎉 用户端数据库初始化完成！');

    } catch (error) {
        console.error('❌ 数据库初始化失败:', error);
        throw error;
    }
}

// 如果直接运行此文件，则执行初始化
if (require.main === module) {
    initializeUserDatabase()
        .then(() => process.exit(0))
        .catch(() => process.exit(1));
}

module.exports = { initializeUserDatabase };